package com.student.dao;

import com.student.model.Student;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * 学生数据库访问类
 *
 * @author 康娜的小肥腿
 */
public class StudentDao {

    /**
     * 查询学生信息
     * @param employee
     * @return
     */
    public List<Student> select(Student employee){
        // 存储占位符的值
        List<Object> values = new ArrayList<>();

//        String sql = "select e.*,m.ename managerName from employee e " +
//                " left join employee m on e.sex=m.empno " +
//                " where 1=1 ";

        String sql = "select e.*,m.ename from employee e " +
                " left join employee m on e.sex=m.empno " +
                " where 1=1 ";

        if (employee.getEname() != null && employee.getEname().trim().length()>0) {
            sql += " and e.ename LIKE ? ";
            values.add("%" + employee.getEname() + "%");
        }
        if (employee.getSno() != null && employee.getSno().trim().length()>0) {
            sql += " and e.sno=? ";
            values.add(employee.getSno());
        }
//        if (employee.getDeptno() != null) {
//            sql += " and e.deptno=? ";
//            values.add(employee.getDeptno());
//        }
        if (employee.getZy() != null && employee.getZy().trim().length()>0) {
            sql += " and e.zy=? ";
            values.add(employee.getZy());
        }

        List<Student> employeeList = new ArrayList<>();

        try (Connection conn = JdbcUtil.getConnection();) {

            PreparedStatement ps = conn.prepareStatement(sql);
            // 设置点位符的值
            for (int i = 0; i < values.size(); i++) {
                ps.setObject(i+1, values.get(i));
            }

            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                employeeList.add(new Student(rs.getInt("empno"),
                        rs.getString("ename"),
                        rs.getString("sno"),
                        rs.getString("sex"),
                        rs.getDate("hiredate"),
                        rs.getString("sal"),
                        rs.getString("phone"),
                        rs.getString("zy")));
            }
        } catch (SQLException e) {
            System.err.println("查询学生信息失败." + e.getMessage());
        }
        return  employeeList;
    }



    /**
     * 增加学生
     * @param emp
     */
    public boolean save(Student emp) {
        String sql = "insert into employee(ename,sno,sex,hiredate,sal,phone,zy) values(?,?,?,?,?,?,?)";
        try (Connection conn = JdbcUtil.getConnection()) {
            PreparedStatement ps = conn.prepareStatement(sql);

            setParameter(emp, ps);

            // insert,delete,update操作返回的int值为数据操作的行数
            int updated = ps.executeUpdate();
            return (updated == 1 ? true : false);

        } catch (SQLException e) {
            System.err.println("增加学生失败。" + e.getMessage());
        }
        return false;
    }

    /**
     *  按主键获取学生信息
     * @param empno
     */
    public Student selectByPrimaryKey(Integer empno) {
        String sql = "select * from employee where empno=?";

        try (Connection conn = JdbcUtil.getConnection();) {
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setInt(1, empno);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                return new Student(rs.getInt("empno"),
                        rs.getString("ename"),
                        rs.getString("sno"),
                        rs.getString("sex"),
                        rs.getDate("hiredate"),
                        rs.getString("sal"),
                        rs.getString("phone"),
                        rs.getString("zy"));
            }

        } catch (SQLException e) {
            System.err.println("按主键获取学生信息失败" + e.getMessage());
        }
        return null;
    }

    /**
     * 更新
     * @param emp
     * @return
     */
    public boolean update(Student emp) {
        String sql = "update employee set ename=?,sno=?,sex=?,hiredate=?,sal=?,phone=?,zy=? where empno=?";
        try (Connection conn = JdbcUtil.getConnection()) {
            PreparedStatement ps = conn.prepareStatement(sql);
            setParameter(emp, ps);
            ps.setInt(8,emp.getEmpno());

            // insert,delete,update操作返回的int值为数据操作的行数
            int updated = ps.executeUpdate();
            return (updated == 1 ? true : false);

        } catch (SQLException e) {
            System.err.println("增加学生失败。" + e.getMessage());
        }
        return false;
    }

    /**
     * 设置点位符参数
     * @param emp
     * @param ps
     * @throws SQLException
     */
    private void setParameter(Student emp, PreparedStatement ps) throws SQLException {
        ps.setString(1, emp.getEname());

        if (emp.getSno() != null && emp.getSno().trim().length() > 0) {
            ps.setString(2, emp.getSno());
        } else {
            // 让底层的JDBC驱动当此字符为null如何处理null值
            ps.setNull(2, Types.VARCHAR);
        }

        if (emp.getSex() != null) {
            ps.setString(3, emp.getSex());
        } else {
            ps.setNull(3, Types.VARCHAR);
        }
        if (emp.getHiredate() != null) {
            // 将java.util.Date转换成java.sql.Date
            ps.setDate(4, new Date(emp.getHiredate().getTime()));
        } else {
            ps.setNull(4, Types.DATE);
        }
        if (emp.getSal() != null) {
            ps.setString(5, emp.getSal());
        } else {
            ps.setNull(5, Types.VARCHAR);
        }
        if (emp.getPhone() != null) {
            ps.setString(6, emp.getPhone());
        } else {
            ps.setNull(6, Types.VARCHAR);
        }
//        if (emp.getDeptno() != null) {
//            ps.setInt(7, emp.getDeptno());
//        } else {
//            ps.setNull(7, Types.INTEGER);
//        }
        if(emp.getZy()!=null){
            ps.setString(7,emp.getZy());
        }else {
            ps.setNull(7,Types.VARCHAR);
        }
    }

    /**
     * 删除
     * @param empno
     * @return
     */
    public boolean delete(Integer empno) {
        try (Connection conn = JdbcUtil.getConnection();) {
            PreparedStatement ps = conn.prepareStatement("delete from employee where empno=?");
            ps.setInt(1,empno);
            int i = ps.executeUpdate();
            return (i==1 ? true : false);

        } catch (SQLException e) {
            System.err.println("删除失败！" + e.getMessage());
        }
        return false;
    }
}
